# -*- coding: UTF-8 -*-
'''
Created on 18/11/2014

@author: Eduardo Cardeira - Student Number: 6033

This module contains the class and methods that select data from the database
according to the user input for later use.
'''

import sqlite3


class DataProcessing:
    """
    The DataProcessing class.
    Contains methods to select and return data from the database according to
    user input.
    """

    def __init__(self, database_name, table_name):
        """
        The instantiation method for DataProcessing.

        Arguments:

        database_name - the name of the database to be used.
        table_name - the name of the database table the data will be selected
        from.
        """
        self.database_name = database_name
        self.table_name = table_name
        pass
    pass

    def connect_database(self):
        """
        The connect_database method.
        Tries to connect to a given database and returns a pointer to said
        database.
        Raises an exception and presents a message in case the operation fails.

        Returns:

        connection - the pointer to the database
        """
        try:
            connection = sqlite3.connect(self.database_name)
            return connection
        except IOError:
            print "Could not connect to " + self.database_name + ". The \
            database file is probably missing and could not be connected to."
            pass
        pass
    pass

    def get_ipc(self, starting_year=1977, ending_year=2009):
        """
        The get_ipc method.
        Extracts from the database the "Indíce de Preços ao Consumidor" values
        for the given year interval.

        Arguments:

        starting_year - the year where the data interval begins.
        1977 is the default starting year.
        ending_year - the year where the data interval ends.
        2009 is the default ending year.
        (There is no "Indíce de Preços ao Consumidor" data after 2009)

        Returns:

        data - the list of "Indíce de Preços ao Consumidor" values in the given
        time interval.
        """
        connection = self.connect_database()
        cursor = connection.cursor()
        cursor.execute(('''SELECT [Ano], [IPC Global (1981 =100)] FROM "{0}"
                       WHERE Ano >= "{1}" AND Ano <= "{2}"''').
                       format(self.table_name, starting_year, ending_year))
        data = cursor.fetchall()
        connection.close()
        return data
        pass
    pass

    def get_ti(self, starting_year=1978, ending_year=2013):
        """
        The get_ti method.
        Extracts from the database the "Taxa de Inflação" values for the given
        year interval.

        Arguments:

        starting_year - the year where the data interval begins.
        1978 is the default starting year.
        (There is no "Taxa de Inflação" data before 1978)
        ending_year - the year where the data interval ends.
        2013 is the default ending year.

        Returns:

        data - the list of "Taxa de Inflação" values in the given time
        interval.
        """
        connection = self.connect_database()
        cursor = connection.cursor()
        cursor.execute(('''SELECT [Ano], [Variação Anual (Taxa de Inflação)]
                       FROM "{0}" WHERE Ano >= "{1}" AND Ano <= "{2}"''').
                       format(self.table_name, starting_year, ending_year))
        data = cursor.fetchall()
        connection.close()
        return data
        pass
    pass

    def get_rminmfp(self, starting_year=1977, ending_year=2012):
        """
        The get_rminmfp method.
        Extracts from the database the "Remuneração Miníma Mensal" values for
        the given year interval.

        Arguments:

        starting_year - the year where the data interval begins.
        1977 is the default starting year.
        ending_year - the year where the data interval ends.
        2012 is the default ending year.
        (There is no "Remuneração Miníma Mensal" data after 2012)

        Returns:

        data - the list of "Remuneração Miníma Mensal" values in the given time
        interval.
        """
        connection = self.connect_database()
        cursor = connection.cursor()
        cursor.execute(('''SELECT [Ano], [remuneração miníma mensal] FROM "{0}"
                       WHERE Ano >= "{1}" AND Ano <= "{2}"''').
                       format(self.table_name, starting_year, ending_year))
        data = cursor.fetchall()
        connection.close()
        return data
        pass
    pass

    def get_rmaxmfp(self, starting_year=1977, ending_year=2012):
        """
        The get_rmaxmfp method.
        Extracts from the database the "Remuneração Máxima Mensal" values for
        the given year interval.

        Arguments:

        starting_year - the year where the data interval begins.
        1977 is the default starting year.
        ending_year - the year where the data interval ends.
        2012 is the default ending year.
        (There is no "Remuneração Máxima Mensal" data after 2012)

        Returns:

        data - the list of "Remuneração Máxima Mensal" values in the given time
        interval.
        """
        connection = self.connect_database()
        cursor = connection.cursor()
        cursor.execute(('''SELECT [Ano], [remuneração máxima mensal] FROM "{0}"
                       WHERE Ano >= "{1}" AND Ano <= "{2}"''').
                       format(self.table_name, starting_year, ending_year))
        data = cursor.fetchall()
        connection.close()
        return data
        pass
    pass

    def get_evominmaxpc(self, starting_year=1978, ending_year=2012):
        """
        The get_evominmaxpc method.
        Extracts from the database the "Evolução das Remunerações Mínimas e
        Máximas Mensais a Preços Constantes" values for the given year
        interval.

        Arguments:

        starting_year - the year where the data interval begins.
        1978 is the default starting year.
        (There is no "Taxa de Inflação" data before 1978)
        ending_year - the year where the data interval ends.
        2012 is the default ending year.
        (There is no "Remuneração Mensal" data after 2012)

        Returns:

        data - the list of "Evolução das Remunerações Mínimas e Máximas Mensais
        a Preços Constantes" values in the given time interval.
        """
        connection = self.connect_database()
        cursor = connection.cursor()
        cursor.execute(('''SELECT [Ano], [Variação Anual (Taxa de Inflação)],
                       [remuneração miníma mensal], [remuneração máxima mensal]
                       FROM "{0}" WHERE Ano >= "{1}" AND Ano <= "{2}"''').
                       format(self.table_name, starting_year, ending_year))
        pre_data = cursor.fetchall()
        # To calculate the "Remunerações Mensais a Preços Constantes" value it
        # is necessary to divide the value of "Remuneração Mensal" for the
        # value of "Taxa de Inflação". Therefore, data will store the newly
        # calculated values we want to send later.
        data = []
        rminmfp = 0.0
        rmaxmfp = 0.0
        ti = 0.0
        minpc = 0.0
        maxpc = 0.0
        for row in pre_data:
            rminmfp = row[2]  # Stores "Remunerção Miníma Mensal" here.
            rmaxmfp = row[3]  # Stores "Remunerção Máxima Mensal" here.
            ti = row[1]       # Stores "Taxa de Inflação" here.
            minpc = rminmfp / ti  # Stores "RMinM a Preços Constantes" here.
            maxpc = rmaxmfp / ti  # Stores "RMaxM a Preços Constantes" here.
            data.append((row[0], minpc, maxpc))
        connection.close()
        return data
        pass
    pass

    def get_pibac(self, starting_year=1977, ending_year=2013):
        """
        The get_pibac method.
        Extracts from the database the "Produto Interno Bruto Anual per Capita"
        values for the given year interval.

        Arguments:

        starting_year - the year where the data interval begins.
        1977 is the default starting year.
        ending_year - the year where the data interval ends.
        2013 is the default ending year.

        Returns:

        data - the list of "Produto Interno Bruto Anual per Capita" values in
        the given time interval.
        """
        connection = self.connect_database()
        cursor = connection.cursor()
        cursor.execute(('''SELECT [Ano], [PIB per capita anual] FROM "{0}"
                       WHERE Ano >= "{1}" AND Ano <= "{2}"''').
                       format(self.table_name, starting_year, ending_year))
        data = cursor.fetchall()
        connection.close()
        return data
        pass
    pass

    def get_rnbac(self, starting_year=1977, ending_year=2013):
        """
        The get_rnbac method.
        Extracts from the database the "Rendimento Nacional Bruto Anual per
        Capita" values for the given year interval.

        Arguments:

        starting_year - the year where the data interval begins.
        1977 is the default starting year.
        ending_year - the year where the data interval ends.
        2013 is the default ending year.

        Returns:

        data - the list of "Rendimento Nacional Bruto Anual per Capita" values
        in the given time interval.
        """
        connection = self.connect_database()
        cursor = connection.cursor()
        cursor.execute(('''SELECT [Ano],
                       [Rendimento nacional bruto per capita anual] FROM "{0}"
                       WHERE Ano >= "{1}" AND Ano <= "{2}"''').
                       format(self.table_name, starting_year, ending_year))
        data = cursor.fetchall()
        connection.close()
        return data
        pass
    pass

    def get_rdbac(self, starting_year=1977, ending_year=2013):
        """
        The get_rdbac method.
        Extracts from the database the "Rendimento Disponível Bruto Anual per
        Capita" values for the given year interval.

        Arguments:

        starting_year - the year where the data interval begins.
        1977 is the default starting year.
        ending_year - the year where the data interval ends.
        2013 is the default ending year.

        Returns:

        data - the list of "Rendimento Disponível Bruto Anual per Capita"
        values in the given time interval.
        """
        connection = self.connect_database()
        cursor = connection.cursor()
        cursor.execute(('''SELECT [Ano],
                       [Rendimento disponível bruto per capita anual]
                       FROM "{0}" WHERE Ano >= "{1}" AND Ano <= "{2}"''').
                       format(self.table_name, starting_year, ending_year))
        data = cursor.fetchall()
        connection.close()
        return data
        pass
    pass

    def get_rca(self, starting_year=1977, ending_year=2013):
        """
        The get_rca method.
        Extracts from the database the "Remunerações per Capita Anuais" values
        for the given year interval.

        Arguments:

        starting_year - the year where the data interval begins.
        1977 is the default starting year.
        ending_year - the year where the data interval ends.
        2013 is the default ending year.

        Returns:

        data - the list of "Remunerações per Capita Anuais" values in the given
        time interval.
        """
        connection = self.connect_database()
        cursor = connection.cursor()
        cursor.execute(('''SELECT [Ano], [Remunerações per capita anual]
                       FROM "{0}" WHERE Ano >= "{1}" AND Ano <= "{2}"''').
                       format(self.table_name, starting_year, ending_year))
        data = cursor.fetchall()
        connection.close()
        return data
        pass
    pass

'''
# Test run of all the methods to verify if the values are correct.
extractor = DataProcessing("Database.db", "Dados")

print "Indíce de Preços ao Consumidor"
data = extractor.get_ipc()
for row in data:
    print row
print "---------------------"

print "Taxa de Inflação"
data = extractor.get_ti()
for row in data:
    print row
print "-------------------------"

print "Renumeração Miníma Mensal"
data = extractor.get_rminmfp()
for row in data:
    print row
print "-------------------------"

print "Renumeração Máxima Mensal"
data = extractor.get_rmaxmfp()
for row in data:
    print row
print "-----------------------------------------------------------------------"

print "Evolução das Remunerações Mínimas e Máximas Mensais a Preços Constantes"
data = extractor.get_evominmaxpc()
for row in data:
    print row
print "--------------------------------------"

print "Produto Interno Bruto Anual per Capita"
data = extractor.get_pibac()
for row in data:
    print row
print "------------------------------------------"

print "Rendimento Nacional Bruto Anual per Capita"
data = extractor.get_rnbac()
for row in data:
    print row
print "--------------------------------------------"

print "Rendimento Disponível Bruto Anual per Capita"
data = extractor.get_rdbac()
for row in data:
    print row
print "------------------------------"

print "Remunerações per Capita Anuais"
data = extractor.get_rca()
for row in data:
    print row
'''
